import xlrd, xlwt
from common.big_screen_interface import BigScreenInterface
import json
import time

global col_0,col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10,col_11,col_12,col_13,col_14,col_15,col_16,col_17,col_18,col_19,col_20

class WriteExcel():
    def __init__(self,sheet_name):
        # 创建一个workbook 设置编码  和worksheet
        self.wb = xlwt.Workbook(encoding='utf-8')
        self.ws = self.wb.add_sheet(sheet_name)

    def write_excel_head(self,head_col0,head_col1,head_col2,head_col3,head_col4,head_col5,head_col6,):
        self.ws.write(0, 0,head_col0)
        self.ws.write(0, 1,head_col1)
        self.ws.write(0, 2,head_col2)
        self.ws.write(0, 3, head_col3)
        self.ws.write(0, 4, head_col4)
        self.ws.write(0, 5, head_col5)
        self.ws.write(0, 6, head_col6)

    def write_excel_body(self, row, col, value):
        # 数据写入excel,参数对应 行, 列, 值
        self.ws.write(row, col, value)  # 不带样式的写入

    def save_excel(self,excel_name):
        current_time = time.strftime("%Y-%m-%d_%H_%M_%S", time.localtime(time.time()))
        self.wb.save('/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/result/data_{}_{}.xls'.format(excel_name,current_time))



class ReadExcel(object):
    def __init__(self, data_path, sheetname, wenxin_step1_url,wenxin_step1_static):
        #定义一个属性接收文件路径
        self.data_path = data_path
        # 定义一个属性接收工作表名称
        self.sheetname = sheetname
        # 使用xlrd模块打开excel表读取数据
        self.data = xlrd.open_workbook(self.data_path)
        # 根据工作表的名称获取工作表中的内容
        self.table = self.data.sheet_by_name(self.sheetname)
        # 获取工作表的有效行数
        self.rowNum = self.table.nrows
        self.wenxin_step1_url = wenxin_step1_url
        self.wenxin_step1_static = wenxin_step1_static

    def readExcel(self):
        data = []
        for i in range(1, self.rowNum):
            # 获取单元格数据
            except_type = self.table.cell_value(i, 1)
            content = self.table.cell_value(i, 2)
            contents = content.split('\n')

            # print(contents)
            for content in contents:
                # 调用文心接口,拿到返回的type和对应的method
                obj = BigScreenInterface(self.wenxin_step1_url)
                res, r_elapsed = obj.wenxin_step1(content)
                res_dic = json.loads(res)
                # 将content，返回type写入excel
                if self.sheetname == "数据分析类指令":
                    # 数据分析指令，需要调用另一个接口
                    obj_statistic = BigScreenInterface(self.wenxin_step1_static)
                    res_statistic, r_elapsed_statistics = obj_statistic.wenxin_step1(content)
                    res_statistic_dic = json.loads(res_statistic)
                    data.append({"content": content, "res": res_dic, "res_statistic_dic": res_statistic_dic,
                                 "except_type": except_type, "r_elapsed_statistics": r_elapsed_statistics,"r_elapsed": r_elapsed})
        return data

def generate_excel_body(data, obj,):
    current_time = time.strftime("%Y-%m-%d_%H_%M_%S", time.localtime(time.time()))
    save_path = '/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/result/data_数据分析_{}.json'.format(current_time)
    with open(save_path,'w',encoding='utf-8') as f:
        for i in range(len(data)):
            # 1：第一列
            col_0 = data[i]["content"]
            # 2：第二列
            try:
                col_1 = data[i]["res"]["data"]["type"]
            except Exception as e:
                col_1 = "无data.type字段"
            # 3:第三列
            col_2 = data[i]["except_type"]
            # 第四列 意图识别结论
            if col_1 == col_2:
                col_3 = "PASS"
            else:
                col_3 = "FAIL"
            if col_3 == "PASS":
                # 第5列   请求聊天内容时间
                try:
                    for m in data[i]["res"]["data"]["trace"]["chatRes"]:
                        if m["text"].startswith("请求聊天内容"):
                            col_4 = int(m["timestamp"])
                except Exception as e:
                    col_4 = "未获取到聊天内容时间"
                # 第6列   控屏返回时间
                try:
                    for m in data[i]["res"]["data"]["trace"]["chatRes"]:
                        if m["text"].startswith("判断控屏返回"):
                            col_5 = int(m["timestamp"])
                except Exception as e:
                    col_5 = "未获取到判断控屏返回时间"
                # 第7列   请求数据分析内容
                try:
                    for m in data[i]["res_statistic_dic"]["data"]["trace"]["chatRes"]:
                        if m["text"].startswith("请求数据分析内容参数"):
                            col_6 = int(m["timestamp"])
                except Exception as e:
                    col_6 = "未获取到请求数据分析内容参数时间"
                # 第8列   请求数据分析内容JSON时间
                try:
                    for m in data[i]["res_statistic_dic"]["data"]["trace"]["chatRes"]:
                        if m["text"].startswith("请求数据分析内容JSON"):
                            col_7 = int(m["timestamp"])
                except Exception as e:
                    col_7 = "未获取到请求数据分析内容JSON时间"
                # 第9列 数据分析返回时间
                try:
                    for m in data[i]["res_statistic_dic"]["data"]["trace"]["chatRes"]:
                        if m["text"].startswith("数据分析返回"):
                            col_8 = int(m["timestamp"])
                except Exception as e:
                    col_8 = "未获取到数据分析返回时间"
                # 第10列 执行的SQL语句
                try:
                    for m in data[i]["res_statistic_dic"]["data"]["trace"]["chatRes"]:
                        if m["text"].startswith("执行的SQL语句"):
                            col_9 = int(m["timestamp"])
                except Exception as e:
                    col_9 = "未获取到执行的SQL语句时间"
                # 第11列 SQL结果转换JSON
                try:
                    for m in data[i]["res_statistic_dic"]["data"]["trace"]["chatRes"]:
                        if m["text"].startswith("SQL结果转换JSON"):
                            col_10 = int(m["timestamp"])
                except Exception as e:
                    col_10 = "未获取到SQL结果转换JSON时间"
                # 第12列 请求文心分析图表
                try:
                    for m in data[i]["res_statistic_dic"]["data"]["trace"]["chatRes"]:
                        if m["text"].startswith("请求文心分析图表"):
                            col_11 = int(m["timestamp"])
                except Exception as e:
                    col_11 = "未获取到请求文心分析图表时间"
                # 第13列 使用图表分析返回
                try:
                    for m in data[i]["res_statistic_dic"]["data"]["trace"]["chatRes"]:
                        if m["text"].startswith("使用图表分析返回"):
                            col_12 = int(m["timestamp"])
                except Exception as e:
                    col_12 = "未获取到使用图表分析返回时间"
                # 第14列 结束请求，开始响应内容
                try:
                    for m in data[i]["res_statistic_dic"]["data"]["trace"]["chatRes"]:
                        if m["text"].startswith("结束请求"):
                            col_13 = int(m["timestamp"])
                except Exception as e:
                    col_13 = "未获取到结束请求，开始响应内容时间"
                # 第15列 总耗时
                try:
                    col_14 = int(data[i]["res_statistic_dic"]["data"]["trace"]["spendTime"])
                except Exception as e:
                    col_14 = "未获取到总耗时"
                # 第16列 意图识别耗时
                try:
                    col_15 = col_5 - col_4
                except Exception as e:
                    col_15 = "获取时间戳失败"
                # 第17列 生成sql耗时
                try:
                    col_16 = col_8 - col_7
                except Exception as e:
                    col_16 = "获取时间戳失败"
                # 第18列 执行sql耗时
                try:
                    col_17 = col_10 - col_9
                except Exception as e:
                    col_17 = "获取时间戳失败"
                # 第19列 生成图表播报耗时
                try:
                    col_18 = col_12 - col_11
                except Exception as e:
                    col_18 = "获取时间戳失败"

                # 第20列 返回的statistic内容
                try:
                    # col_19 = str((data[i]["res_statistic_dic"]["data"]["statistic"]))
                    col_19 = json.dumps((data[i]["res_statistic_dic"]["data"]["statistic"]),ensure_ascii=False)
                except Exception as e:
                    col_19 = "未获取到返回的statistic内容"
                # 第21列 备注
                try:
                    # col_20 = str((data[i]["res_statistic_dic"]["data"]))
                    col_20 = json.dumps((data[i]["res_statistic_dic"]["data"]),ensure_ascii=False)
                except Exception as e:
                    col_20 = json.dumps((data[i]["res_statistic_dic"]),ensure_ascii=False)
            else:
                col_4 = ''
                col_5 = ''
                col_6 = ''
                col_7 = ''
                col_8 = ''
                col_9 = ''
                col_10 = ''
                col_11 = ''
                col_12 = ''
                col_13 = ''
                col_14 = ''
                col_15 = ''
                col_16 = ''
                col_17 = ''
                col_18 = ''
                col_19 = ''
                col_20 = json.dumps((data[i]["res"]),ensure_ascii=False)
            json_data = {'col_0':col_0,'col_1':col_1,'col_2':col_2,'col_3':col_3,'col_4':col_4,
                         'col_5':col_5,'col_6':col_6,'col_7':col_7,'col_8':col_8,
                         'col_9':col_9,'col_10':col_10,'col_11':col_11,'col_12':col_12,
                         'col_13':col_13,'col_14':col_14,'col_15':col_15,'col_16':col_16,
                         'col_17':col_17,'col_18':col_18,'col_19':col_19,'col_20':col_20,}
            json.dump(json_data, f,ensure_ascii = False)
            json_data.clear()




def main(data_path,sheetname,wenxin_step1_url,wenxin_step1_static):
    read_obj = ReadExcel(data_path, sheetname, wenxin_step1_url, wenxin_step1_static)
    data = read_obj.readExcel()

    # write_obj = WriteExcel(sheetname)
    # if sheetname == "控屏类指令":
    #     write_obj.write_excel_head("指令类型","返回的type类型","返回的screenControl.method值","预期的type类型","预期的screenControl.method值","结论","备注")
    # elif sheetname == "闲聊类指令":
    #     write_obj.write_excel_head("指令类型", "返回的type类型", "返回的chat.type值", "预期的type类型","预期的chat.type值", "结论","备注")
    # else:
    #     write_obj.write_excel_head(
    #         "指令类型", "返回意图识别type类型", "预期意图识别type类型", "意图识别结论",
    #         "请求聊天内容时间","控屏返回时间",
    #         "请求数据分析内容时间","请求数据分析内容JSON时间","数据分析返回时间","执行sql时间","sql结果转为JSON时间","请求文心分析图标时间",
    #         "使用图标分析返回时间","请求结束，开始响应时间",
    #         "总耗时","意图识别耗时","生成sql耗时","执行sql耗时","生成图表播报耗时","返回的statistic内容","备注"
    #     )
    generate_excel_body(data, "save_path")


if __name__ == "__main__":

    data_path = "/bigScreenDigitalPeople/resource/大屏数字人指令集-test.xlsx"
    # sheetname_list = ["控屏类指令","闲聊类指令","数据分析类指令"]
    # sheetname_list = ["控屏类指令"]
    # sheetname_list = ["闲聊类指令"]
    sheetname_list = ["数据分析类指令"]
    wenxin_step1_url = "https://219.135.214.196:9001/v2x/platform/wenxin/chat"
    wenxin_step1_static = "https://219.135.214.196:9001/v2x/platform/wenxin/chat/statistic"

    for sheetname in sheetname_list:

        main(data_path,sheetname,wenxin_step1_url,wenxin_step1_static)








